﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace ConcordWinManager
{
    /// <summary>
    /// Консультант
    /// </summary>
    public class Consultant
    {
        #region[Fields]
        /// <summary>
        /// Идентификатор записи консультанта в БД
        /// </summary>
        public int id_consultant;

        /// <summary>
        /// Фамилия
        /// </summary>
        public string Family;

        /// <summary>
        /// Имя
        /// </summary>
        public string Name;

        /// <summary>
        /// Отчество
        /// </summary>
        public string FName;

        /// <summary>
        /// Фамилия в родительном падеже
        /// </summary>
        public string Family2;

        /// <summary>
        /// Имя в родительном падеже
        /// </summary>
        public string Name2;

        /// <summary>
        /// Отчество в родительном падеже
        /// </summary>
        public string FName2;

        /// <summary>
        /// Идентификатор записи резидента в БД
        /// </summary>
        public int id_rezident;

        /// <summary>
        /// Идентификатор адреса в БД
        /// </summary>
        public int id_address;

        /// <summary>
        /// Адрес
        /// </summary>
        public Address address;

        /// <summary>
        /// Резидент
        /// </summary>
        public Rezident rezident;

        public int id_doverennost;

        public Doverennost doverennost;

        /// <summary>
        /// Пользователь
        /// </summary>
        public User user;

        #endregion

        #region[Constructors]

        /// <summary>
        /// Конструктор по умолчанию
        /// </summary>
        public Consultant()
        {
            id_consultant = 0;
            Family = string.Empty;
            Name = string.Empty;
            FName = string.Empty;
            id_rezident = 0;
            id_address = 0;
            rezident = new Rezident();
            address = new Address();
            user = new User();
            doverennost = new Doverennost();
        }

        /// <summary>
        /// Конструктор с параметрами
        /// </summary>
        /// <param name="id_consultant">Идентификатор консультанта</param>
        /// <param name="Family">Фамилия</param>
        /// <param name="Name">Имя</param>
        /// <param name="FName">Отчетсво</param>
        /// <param name="id_rezident">Идентификатор резидента</param>
        /// <param name="id_address">Идентификатор адреса</param>
        public Consultant(int id_consultant, string Family, string Name, string FName,  int id_rezident, int id_address)
        {
            this.id_consultant = id_consultant;
            this.Family = Family;
            this.Name = Name;
            this.FName = FName;
            this.id_rezident = id_rezident;
            this.id_address = id_address;
        }

        #endregion

        #region[Methods]
        /// <summary>
        /// Добавление в бд
        /// </summary>
        public void Insert()
        {
            string query = string.Format("INSERT INTO dbo.Consultant " +
                " (Family, Name, FName, Family2, Name2, FName2, id_rezident, id_address) " +
                " VALUES ('{0}','{1}', '{2}', '{3}','{4}', '{5}', {6}, {7})",                
                this.Family,
                this.Name,
                this.FName,
                this.Family2,
                this.Name2,
                this.FName2,
                this.rezident.id_rezident != 0 ? this.rezident.id_rezident.ToString() : "NULL",
                this.address.id_address != 0 ? this.address.id_address.ToString() : "NULL"
                );

            DB.execute_sql(query);
            this.id_consultant = DB.max_num("dbo.Consultant", "id_consultant");

        }

        public Consultant SelectById(int id)
        {
            DataSet ds = new DataSet();
            DB.query_table("SELECT * FROM dbo.Consultant WHERE id_consultant = " + id, "dbo.Consultant", ref ds);

            this.id_consultant = int.Parse(ds.Tables["dbo.Consultant"].Rows[0]["id_consultant"].ToString());
            this.Family = ds.Tables["dbo.Consultant"].Rows[0]["Family"].ToString();
            this.Name = ds.Tables["dbo.Consultant"].Rows[0]["Name"].ToString();
            this.FName = ds.Tables["dbo.Consultant"].Rows[0]["FName"].ToString();
            this.Family2 = ds.Tables["dbo.Consultant"].Rows[0]["Family2"].ToString();
            this.Name2 = ds.Tables["dbo.Consultant"].Rows[0]["Name2"].ToString();
            this.FName2 = ds.Tables["dbo.Consultant"].Rows[0]["FName2"].ToString();
            this.id_rezident = int.Parse(ds.Tables["dbo.Consultant"].Rows[0]["id_rezident"].ToString());        
            if (int.TryParse(ds.Tables["dbo.Consultant"].Rows[0]["id_address"].ToString(), out this.id_address))
            {
                ;//без ошибки, тырим адрес
            }

            user.SelectByConsultantId(this.id_consultant);

            doverennost.SelectByConsultantId(this.id_consultant);

            return this;
        }

        public void SelectByArgs()
        {
            ;
        }

        /// <summary>
        /// Запрос всех
        /// </summary>
        /// <param name="ds"></param>
        public void SelectAll(ref DataSet ds)
        {
            DB.query_table("SELECT * FROM dbo.View_Consultant ", "dbo.View_Consultant", ref ds);
        }

        /// <summary>
        /// Обновление записи
        /// </summary>
        public void Update()
        {
            string query = string.Format("UPDATE dbo.Consultant SET Family='{0}', Name='{1}', " +
                " FName='{2}', Family2='{3}', Name2='{4}',FName2='{5}', id_address={6}, id_rezident={7} WHERE (id_consultant = '{8}')",
                this.Family,
                this.Name,
                this.FName,
                this.Family2,
                this.Name2,
                this.FName2,
                this.address.id_address != 0 ? this.address.id_address.ToString() : "NULL",
                this.rezident.id_rezident != 0 ? this.rezident.id_rezident.ToString() : "NULL",
                this.id_consultant);

            DataSet ds = new DataSet();

            DB.query_table(query, "dbo.Counsultant", ref ds);
        }

        public void DeleteById()
        {
            ;
        }

        #endregion
    }
}
